package com.sq.partner.manager.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;









import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.IGatewayVatDao;
import com.sq.partner.manager.mybatis.model.GatewayVatRate;
import com.sq.partner.manager.mybatis.model.VatFromDto;
import com.sq.partner.manager.util.JDBCUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.StrUtil;
/**
 * 
 * @author dinghao
 */
@Repository
public class GatewayVatDaoImpl extends BaseDaoImpl implements IGatewayVatDao {
	private static Logger logger = LogUtil.getLog(BaseDaoImpl.class);	
	
	VatFromDto vatFrom =null;
	@Override
	public Map <String ,VatFromDto>   queryFromVat() {
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs= null;
		Map <String ,VatFromDto>  map =new HashMap<String ,VatFromDto>();
		String sql="SELECT CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END  "
				+ "CORE_RISK_CODE,YWJGDM DEPT_CODE,"
				+ "CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END "
				+ "VAT_RISK_CODE,SL VAT_RATE,YWCPDM N_RISK_CODE FROM NVAT_PRODUCTTAX";
		try {
			conn=JDBCUtil.JDBC();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				vatFrom= new VatFromDto();
				if(rs.getString("CORE_RISK_CODE").length()==7 && StrUtil.isNotEmpty(rs.getString("N_RISK_CODE"))){
					VatFromDto nVatFrom =new VatFromDto();
					nVatFrom.setCoreRiskCode(rs.getString("N_RISK_CODE"));
					nVatFrom.setDeptCode(rs.getString("DEPT_CODE"));
					nVatFrom.setVatRate(rs.getBigDecimal("VAT_RATE"));
					nVatFrom.setVatRiskCode(rs.getString("N_RISK_CODE"));
					map.put(rs.getString("N_RISK_CODE")+rs.getString("DEPT_CODE"), nVatFrom);
				}
				vatFrom.setCoreRiskCode(rs.getString("CORE_RISK_CODE"));
				vatFrom.setDeptCode(rs.getString("DEPT_CODE"));
				vatFrom.setVatRate(rs.getBigDecimal("VAT_RATE"));
				vatFrom.setVatRiskCode(rs.getString("VAT_RISK_CODE"));
				map.put(rs.getString("CORE_RISK_CODE")+rs.getString("DEPT_CODE"), vatFrom);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("执行异常",e);
		}finally{
		JDBCUtil.close(conn, pst, rs);
		}
		return map;
	}
	
	
	@Override
	public List<Object[]> queryListObjectsFromVat() {
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs= null;
		List<Object[]> lists=new ArrayList<Object[]>();
		SimpleDateFormat sf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String sql="SELECT CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END  "
				+ "CORE_RISK_CODE,YWJGDM DEPT_CODE,"
				+ "CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END "
				+ "VAT_RISK_CODE,SL VAT_RATE,YWCPDM N_RISK_CODE FROM NVAT_PRODUCTTAX";
		try {
			conn=JDBCUtil.JDBC();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while(rs.next()){
				Object[] obj=new Object[6];
				if(rs.getString("CORE_RISK_CODE").length()==7 && StrUtil.isNotEmpty(rs.getString("N_RISK_CODE"))){
					obj[0]=rs.getString("N_RISK_CODE");
					obj[3]=rs.getString("N_RISK_CODE");
				}else{
					obj[0]=rs.getString("CORE_RISK_CODE");
					obj[3]=rs.getString("VAT_RISK_CODE");
				}
				obj[1]=rs.getString("DEPT_CODE");
				obj[2]=rs.getBigDecimal("VAT_RATE");
				obj[4]="1";//有效
				obj[5]=new Timestamp(sf.parse(sf.format(new Date())).getTime()) ;
				lists.add(obj);
			}
		} catch (Exception e) {
			logger.error("执行异常",e);
		}finally{
		JDBCUtil.close(conn, pst, rs);
		}
		return lists;
	}
	
	
	@Override
	public boolean updateVat(GatewayVatRate gatewayVatRate) throws Exception {
		// TODO Auto-generated method stub
		return this.update("updateGatewayVatRateNotNull", gatewayVatRate);
	}
	@Override
	public GatewayVatRate queryToVat(GatewayVatRate gatewayVatRate) throws SQLException {
		// TODO Auto-generated method stub
		return  (GatewayVatRate) this.query("queryGatewayVatRateByMap", gatewayVatRate);
	}
	@Override
	public boolean addVat(GatewayVatRate gatewayVatRate) throws Exception {
		// TODO Auto-generated method stub
		return this.add("insertGatewayVatRate", gatewayVatRate);
	}
	@Override
	public Page queryPage(Page page) {
		// TODO Auto-generated method stub
		  try {
			Map<String,Object> map = (Map<String, Object>) page.getType();
			long total =(Long) this.query("queryVatCount", map);
			List<GatewayVatRate> list = queryList("queryGatewayVatRatePageOfByMap", map);
			page.setTotal(total);
			page.setData(list);
			Map<String,Object> resultMap = new HashMap<String, Object>();
			page.setMap(resultMap);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("执行异常", e);
		}
		return page;
		
		
	}
	@Override
	public VatFromDto querySingleFromVat(GatewayVatRate gatewayVatRate) {
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs= null;
		String sql="SELECT * FROM ("
				+ "SELECT CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END  "
				+ "CORE_RISK_CODE,YWJGDM DEPT_CODE,"
				+ "CASE WHEN LENGTH(CWCPDM)=8 THEN "
				+ "SUBSTR(CWCPDM,2,LENGTH(CWCPDM)-1) "
				+ "ELSE YWCPDM END "
				+ "VAT_RISK_CODE,SL VAT_RATE FROM NVAT_PRODUCTTAX)"
				+ " WHERE CORE_RISK_CODE=? AND DEPT_CODE =?";
		try {
			conn=JDBCUtil.JDBC();
			pst=conn.prepareStatement(sql);
			pst.setString(1, gatewayVatRate.getCoreRiskCode());
			pst.setString(2, gatewayVatRate.getDeptCode());
			rs=pst.executeQuery();
			while(rs.next()){
				vatFrom= new VatFromDto();
				vatFrom.setCoreRiskCode(rs.getString("CORE_RISK_CODE"));
				vatFrom.setDeptCode(rs.getString("DEPT_CODE"));
				vatFrom.setVatRate(rs.getBigDecimal("VAT_RATE"));
				vatFrom.setVatRiskCode(rs.getString("VAT_RISK_CODE"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("执行异常",e);
		}finally{
		JDBCUtil.close(conn, pst, rs);
		}
		return vatFrom;
	}
	
	@Override
	public boolean BatchInsertVatRate(List<Object[]> params) {
		boolean flag=false;
		try{
			String delsql="DELETE FROM T_GATEWAY_VAT_RATE_TRANSFER";
			 ExecuteSql(delsql);//先删除中转表旧数据，否则会违反唯一约束
			String sql="INSERT INTO T_GATEWAY_VAT_RATE_TRANSFER (CORE_RISK_CODE,DEPT_CODE,VAT_RATE,VAT_RISK_CODE,STATUS,UPDATE_TIME) VALUES (?,?,?,?,?,?)";
			flag=BatchExecute(sql,params);
		}catch(Exception e){
			logger.error("批量执行出错"+e.getMessage());
		}
		return flag;
	}
	
	public void ExecuteSql(String sql){
		Connection conn=null;
		PreparedStatement pds=null;
		try {
			 conn=this.getConnection();
			 pds=conn.prepareStatement(sql);
			 pds.execute();
		} catch (Exception e) {
			logger.error("sql执行出错"+e.getMessage());
		}finally{
			try {
				if(pds!=null){
					pds.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				logger.info("释放连接错误",e);
			}
		}
	}
	
	public  boolean BatchExecute(String sql,List<Object[]> params){
      boolean result=false;
	    PreparedStatement toPstmt=null;
		Connection conn=null;
		int count=0;
			try{
				  conn = this.getConnection();
				  long time1= System.currentTimeMillis();
				  conn.setAutoCommit(false);// 设置手动提交
		            // 关闭自动提交
		           toPstmt=conn.prepareStatement(sql);
		            for (int i = 0; i <params.size(); i++) {
		            	Object[] pars=params.get(i);
		            	for(int j=0;j<pars.length;j++){
		            		toPstmt.setObject((j+1), pars[j]);
		            	}
		            	toPstmt.addBatch(); 
		                if ((i+1) % 1000 == 0){//1000条提交一次
		                	int count2[]=toPstmt.executeBatch();
		                    conn.commit();
		                    count=count+count2.length;
		                    Thread.sleep(1);
		                }
		            }
		            
		   // 提交剩余的批处理
				int count3[]=toPstmt.executeBatch();  
				count=count+count3.length;
				toPstmt.clearBatch();  
				conn.commit();  
				logger.info("JDBC批量操作执行结束，时间:"+ (System.currentTimeMillis()-time1));
				result=true;
				logger.info("共执行条数："+count);
			} catch (Exception e) {
				logger.error("操作数据错误",e);
			}finally{
				// 释放连接
				try {
					if(toPstmt!=null){
						toPstmt.close();
					}
					if(conn!=null){
						conn.close();
					}
				} catch (SQLException e) {
					logger.info("释放连接错误",e);
				}
			}

    return result;
 }


	@Override
	public boolean DelOldVatAndCopyNewVatRate() throws Exception {
		String sql="{call COPY_VAT_RATE}";
		 Connection conn = null;  
	     CallableStatement call = null;
	     boolean flag=true;
	     try {
	            conn =this.getConnection();  
	            call = conn.prepareCall(sql);
	            call.execute();
		} catch (Exception e) {
			flag=false;
			logger.error("执行存储过程 COPY_VAT_RATE 出错"+e);
		}finally{
			if(call!=null){
				call.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
	    return flag;
	}

}
